package com.space.config;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SqliteUtil  {
    public static Connection  conn=null;
    /*static {
        String url = "jdbc:sqlite:sample.db";
        try {
            conn = DriverManager.getConnection(url);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }*/
    public static Connection getConn(){
        String url = "jdbc:sqlite:sample.db";
        try {
            conn = DriverManager.getConnection(url);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
        return conn;
    };

    public void createNewTable(String value) {
        String sql = "";
        if(value.isEmpty()){
            sql = "CREATE TABLE IF NOT EXISTS users (\n"
                    + "    id integer PRIMARY KEY,\n"
                    + "    name text \n"
                    + ");";
        }
        else sql = value;

        try (Statement stmt = conn.createStatement()) {
            stmt.execute(sql);
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public void insert(String sql,String ...value) {
        if(sql.isEmpty()) sql = "INSERT INTO users(name) VALUES(?)";

        try (
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, value[0]);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
    public void insert(String ...value) {
        String sql = "INSERT INTO users(id,name) VALUES(?,?)";

        try (
                PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, Integer.valueOf(value[0]));
            pstmt.setString(2, value[1]);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }


    public void selectAll(String sql) {
        if(sql.isEmpty()) sql = "SELECT id, name FROM users";

        try (
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            // loop through the result set
            while (rs.next()) {
                System.out.println(rs.getInt("id") + "\t" +
                        rs.getString("name") );
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public void update(String sql,int id, String name) {
        if(sql.isEmpty()) sql = "UPDATE users SET name = ? "
                + "WHERE id = ?";

        try (
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // set the corresponding param
            pstmt.setString(1, name);
            pstmt.setInt(2, id);
            // update
            pstmt.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public void delete(String sql,int id) {
        if(sql.isEmpty()) sql = "DELETE FROM users WHERE id = ?";

        try (
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // set the corresponding param
            pstmt.setInt(1, id);
            // execute the delete statement
            pstmt.executeUpdate();

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public static void main(String[] args) {
        SqliteUtil app = new SqliteUtil();
//        app.createNewTable("");
        app.insert(new String[]{"4","dgd"});
//        app.insert("","Bob");
//        app.selectAll("");
//        app.update("",1, "Alicia");
//        app.delete("",1);
        app.selectAll("");
    }
}

